---

Download

Note that there is a separate version of the sample database for SQL Anywhere 11 and 12, so please make sure to download the correct version.

AdventureWorks2008 Sample Database for SQL Anywhere 11

---

Introduction

For the purposes of demonstrating features, as well providing fictitious data for use in demonstrative applications, Microsoft has created the ‘AdventureWorks Cycling’ OLTP database. It is used heavily in examples, and serves as a common channel for developer discussion. It is destined for use with Microsoft’s SQL Server 2008 line of products. In order to facilitate the introduction of SQL Anywhere to Microsoft-Environment Developers, the sample database can be rebuilt easily in SQL Anywhere. Doing so will hopefully demonstrate the ease-of-integration of SQL Anywhere into an existing SQL Server environment, as well as highlight key Microsoft-Environment support features.

The AdventureWorks2008 sample database for SQL Anywhere is used as the data repository for the AdventureWorks2008 .NET samples and the AdventureWorks2008 Windows Mobile samples, highlighting the key .NET features of SQL Anywhere.

As most of the published .NET literature makes use of the AdventureWorks 2008 database, a SQL Anywhere version of this database is used to offer developers a familiar experience when learning about .NET technology, in particular those developers who are looking to create desktop and mobile database applications. For this reason, we kept the database schema definition as close as possible to the SQL scripts included with AdventureWorks 2008.

Where to Obtain Software

The ‘AdventureWorks2008’ database setup scripts are provided for your convenience. These scripts are derived from those available at Microsoft’s CodePlex Site (http://www.codeplex.com/SqlServerSamples). iAnywhere’s SQL Anywhere Developer Edition is provided for your convenience, though you may download the installer yourself from iAnywhere’s Downloads Site.

Licensing & Restrictions

By installing Microsoft SQL Server 2008 and/or the AdventureWorks database, you agree to Microsoft’s terms and conditions. The license agreement for the distribution of the ‘AdventureWorks2008’ database setup scripts is included. Please take the time to review all of Microsoft’s terms and conditions before continuing. MicrosoftR, SQL ServerR, AdventureWorksR, and associated names and logos are the property and copyright of MicrosoftR Corporation. iAnywhere is not responsible for the installation or support of any MicrosoftR product. The following is intended to serve only as example and reference only. SQL AnywhereR comes with its own set of license agreements, which you must review and agree to before continuing.

Rebuilding the Database

The build process has been scripted for your convenience. Simply double-click or run the ‘Rebuild.bat’ script file, located in the ‘Rebuild’ folder. This may take several minutes to complete. When finished, it will place the files “AdventureWorks2008.db” and “AdventureWorks2008.log” in the folder.

Requirements

• SQL Anywhere - Please note that there is a separate version of the sample database for SQL Anywhere 11 and SQL Anywhere 12.
• The modified ‘AdventureWorks2008’ database setup scripts (located in the ‘Rebuild’ folder)

Database Differences

As SQL Server 2008 and SQL Anywhere are two very different database solutions, the ‘AdventureWorks2008’ database will appear slightly different, and offer a few functional differences. These incongruities are the result of different feature sets that are intrinsic to each database’s design. These database differences required modifications to the original AdventureWorks database, as described below.

Users, Groups, and Schemas

You may find that the ‘dbo’ user and all of its objects are not visible in Sybase Central. This is because ‘dbo’ is considered a system user, and is the owner of system objects. To that end, its objects are often filtered from sight so as to minimize unnecessary information. To view these objects, right click on the database in Sybase Central and click ‘Configure Owner Filter…’ and select ‘dbo’.

In SQL Server, ‘schemas’ refer to groupings of objects that can be owned by a single user. In SQL Anywhere, the same functionality is achieved in what are termed ‘groups’. As such, ‘HumanResources’, ‘Person’, ‘Production’, ‘Purchasing’, and ‘Sales’ are all created as groups which own the various database objects.

Language Support

For performance considerations, and based on developer history with SQL Anywhere, the supported languages are ‘Watcom SQL’ and ‘Transact SQL (T-SQL)’. Microsoft SQL Server, however, makes use of its own SQL dialect. For this reason, not all features are natively supported by SQL Anywhere. The stored procedures, functions, and triggers that appear in the SQL Anywhere migration of the ‘AdventureWorks2008’ database are an interpreted translation of those presented in the original SQL Server database. The following language differences required schema changes to the AdventureWorks 2008 database:

• SQL Server uses the TRY/CATCH statement for error handling. SQL Anywhere can also handle errors or exceptions, but it does so very differently using the EXCEPTION keyword.
• Recursive queries in SQL Anywhere require the WITH RECURSIVE clause.
• Indexed views, referred to as materialized views in the SQL Anywhere documentation, are defined differently. SQL Server uses the CREATE VIEW WITH SCHEMABINDING statement, while SQL Anywhere uses the CREATE MATERIALIZED VIEW statement.
• SQL Anywhere supports the SQL SECURITY INVOKER clause on procedure declarations to provide the same functionality as SQL Server's EXECUTE AS CALLER clause.
• SQL Anywhere has BINARY and LONG BINARY data types to support BLOB data, and VARCHAR and LONG VARCHAR types to support CLOB data. To minimize the number of changes to the AdventureWorks 2008 database, the original VARCHAR data type is kept for the image columns, but is truncated to 32,767 bytes as image data is not important to run the .NET samples.
• SQL Anywhere uses spatial reference systems (SRS) and spatial reference identifiers (SRID) to define planar or spheroid spatial data types. The GEOGRAPHY data type in SQL Server is converted to ST_Geometry type with SRID=4326 for simplicity and the original spatial data is then re-formatted to a well-known text form to allow bulk import operation.

For simplicity, the following objects were dropped from the original AdventureWorks 2008 database due to language incompatibilities:

• ddlDatabaseTriggerLog
• dbo.DatabaseLog.*
• dbo.uspLogError
• dbo.uspPrintError
• dbo.uspGetBillOfMaterials
• dbo.uspGetEmployeeManagers
• dbo.uspGetManagerEmployees
• dbo.uspGetWhereUsedProductID
• dbo.uspSearchCandidateResumes
• dbo.ufnGetContactInformation
• HumanResources.Employee.OrganizationLevel
• HumanResources.Employee.IX_Employee_OrganizationLevel_OrganizationNode (Relevant columns not created)
• HumanResources.vJobCandidate
• HumanResources.vJobCandidateEducation
• HumanResources.vJobCandidateEmployment
• Person.Person.XMLPATH_Person_Demographics
• Person.Person.XMLPROPERTY_Person_Demographics
• Person.Person.XMLVALUE_Person_Demographics
• Person.vAdditionalContactInfo
• Person.vStateProvinceCountryRegion.IX_vStateProvinceCountryRegion
• Production.Document.DocumentID
• Production.ProductDocument.DocumentID
• Production.ProductModel.PXML_ProductModel_CatalogDescription
• Production.ProductModel.PXML_ProductModel_Instructions
• Production.vProductAndDescription.IX_vProductAndDescription
• Production.vProductModelCatalogDescription
• Production.vProductModelInstructions
• Sales.Store.PXML_Store_Demographics
• Sales.vPersonDemographics
• Sales.vStoreWithDemographics
• Sales.vSalesPersonSalesByFiscalYear

To keep the SQL translation to a minimum, the following functional differences were implemented:

• HumanResources.uspUpdateEmployeeHireInfo does not execute as the caller, nor does it use TRY/CATCH and will throw exceptions to the user instead of logging them.
• HumanResources.uspUpdateEmployeeLogin does not execute as the caller, nor does it use TRY/CATCH and will throw exceptions to the user instead of logging them.
• HumanResources.uspUpdateEmployeePersonalInfo does not execute as the caller, nor does it use TRY/CATCH and will throw exceptions to the user instead of logging them.
• HumanResources.dEmployee does not indicate the severity or state in its warning, and cannot be disabled during synchronization.
• Person.iuPerson will trigger regardless of which columns are updated, and does not update the ‘Demographics’.
• Production.Document.DocumentNode has been replaced by Production.Document.DocumentNodeID which is an integer primary key, since hierarchyid columns are not supported.
• Production.ProductDocument.DocumentNode has been replaced by Production.Document.DocumentNodeID which is the corresponding integer for the document node (the foreign key was updated as well).
• Production.iWorker does not use TRY/CATCH and will throw exceptions to the user instead of logging them.
• Production.uWorkOrder does not use TRY/CATCH and will throw exceptions to the user instead of logging them.
• Purchasing.iPurchaseOrderDetail does not use TRY/CATCH and will throw exceptions to the user instead of logging them.
• Purchasing.uPurchaseOrderDetail does not use TRY/CATCH and will throw exceptions to the user instead of logging them.
• Purchasing.uPurchaseOrderHeader does not use TRY/CATCH and will throw exceptions to the user instead of logging them, and it will update ‘RevisionNumber’ regardless of whether only ‘Status’ is modified.
• Purchasing.dVendor does not use TRY/CATCH and will throw exceptions to the user instead of logging them, and cannot be disabled during synchronization.
• Sales.iduSalesOrderDetail does not use TRY/CATCH and will throw exceptions to the user instead of logging them, nor does it make the distinction between which columns are affected and will execute the procedure regardless.
• Sales.uSalesOrderHeader does not use TRY/CATCH and will throw exceptions to the user instead of logging them, and it will update ‘RevisionNumber’ regardless of whether only ‘Status’ is modified.